package org.csu.myPetStore.persistence.impl;

import org.csu.myPetStore.domain.CloseSource;
import org.csu.myPetStore.domain.Product;
import org.csu.myPetStore.persistence.DBUtil;
import org.csu.myPetStore.persistence.ProductDAO;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

public class ProductDAOImpl implements ProductDAO {

    private static final String GET_PRODUCT_STRING= "SELECT PRODUCTID, NAME, DESCN as description, CATEGORY as categoryId FROM PRODUCT WHERE PRODUCTID = ?";
    private static final String GET_PRODUCT_LIST_BY_product_STRING= "SELECT PRODUCTID,NAME, DESCN as description,CATEGORY  as categoryId FROM PRODUCT WHERE CATEGORY = ?";
    private static final String SEARCH_PRODUCT_LIST_STRING = " SELECT PRODUCTID, NAME, DESCN as description,CATEGORY  as categoryId from PRODUCT WHERE lower(name) like ?";
    private static final String GET_PRODUCT_ID_BY_NAME = "SELECT PRODUCTID FROM PRODUCT WHERE NAME = ?";

    @Override
    public List<Product> getProductListByCategory(String categoryId) {
        List<Product> productList = new ArrayList<Product>();

        try {
            Connection connection = DBUtil.getConnection();
            PreparedStatement preparedStatement = connection.prepareStatement(GET_PRODUCT_LIST_BY_product_STRING);
            preparedStatement.setString(1,categoryId);

            ResultSet resultSet = preparedStatement.executeQuery();
            while (resultSet.next()){
                Product product = new Product();
                product.setProductId(resultSet.getString(1));
                product.setName(resultSet.getString(2));
                product.setDescription(resultSet.getString(3));
                product.setCategoryId(resultSet.getString(4));
                productList.add(product);
            }

            CloseSource closeSource = new CloseSource();
            closeSource.closeSource(connection,preparedStatement,resultSet,null);


        }catch (Exception e){
            e.printStackTrace();
        }

        return productList;
    }

    @Override
    public Product getProduct(String productId) {

        Product product= null;

        try {
            Connection connection = DBUtil.getConnection();
            PreparedStatement preparedStatement = connection.prepareStatement(GET_PRODUCT_STRING);
            preparedStatement.setString(1,productId);
            ResultSet resultSet = preparedStatement.executeQuery();
            if(resultSet.next()){
                product = new Product();
                product.setProductId(resultSet.getString(1));
                product.setName(resultSet.getString(2));
                product.setDescription(resultSet.getString(3));
                product.setCategoryId(resultSet.getString(4));
            }

            CloseSource closeSource = new CloseSource();
            closeSource.closeSource(connection,preparedStatement,resultSet,null);

        }catch (Exception e){
            e.printStackTrace();
        }
        return product;
    }

    @Override
    public List<Product> searchProductList(String keywords) {
        List<Product> productList = new ArrayList<Product>();
//        System.out.println("keywords:" + keywords);

        try {
            Connection connection = DBUtil.getConnection();
            PreparedStatement preparedStatement = connection.prepareStatement(SEARCH_PRODUCT_LIST_STRING);
            preparedStatement.setString(1,keywords);

            ResultSet resultSet = preparedStatement.executeQuery();
            while (resultSet.next()){
                Product product = new Product();
                product.setProductId(resultSet.getString(1));
                product.setName(resultSet.getString(2));
                product.setDescription(resultSet.getString(3));
                product.setCategoryId(resultSet.getString(4));
//                System.out.println("FFF find" + resultSet.getString(2));
                productList.add(product);
            }

            CloseSource closeSource = new CloseSource();
            closeSource.closeSource(connection,preparedStatement,resultSet,null);


        }catch (Exception e){
            e.printStackTrace();
        }

        return productList;
    }

    @Override
    public List <String> searchProductStringList(String keywords) {
        List <String> StringList = new ArrayList<String>();
        List<Product> productList = new ArrayList<Product>();

//        System.out.println("keywords:" + keywords);
//        System.out.println("Into");

        try {
            Connection connection = DBUtil.getConnection();
            PreparedStatement preparedStatement = connection.prepareStatement(SEARCH_PRODUCT_LIST_STRING);
            preparedStatement.setString(1,keywords);

            ResultSet resultSet = preparedStatement.executeQuery();

            if(!resultSet.next())
                System.out.println("Result null");

            while (resultSet.next()){
                Product product = new Product();
                product.setProductId(resultSet.getString(1));
                product.setName(resultSet.getString(2));
                product.setDescription(resultSet.getString(3));
                product.setCategoryId(resultSet.getString(4));
                System.out.println("FFF find" + resultSet.getString(2));
                productList.add(product);
            }

            CloseSource closeSource = new CloseSource();
            closeSource.closeSource(connection,preparedStatement,resultSet,null);


        }catch (Exception e){
            e.printStackTrace();
        }

        for(int i=0;i<productList.size();i++){
            StringList.add(productList.get(i).getName());
        }

        return StringList;
    }

    @Override
    public String getProductIdByName(String name) {

        String ID="";

        try {
            Connection connection = DBUtil.getConnection();
            PreparedStatement preparedStatement = connection.prepareStatement(GET_PRODUCT_ID_BY_NAME);
            preparedStatement.setString(1,name);

            ResultSet resultSet = preparedStatement.executeQuery();
            if (resultSet.next()){
                ID = resultSet.getString(1);
            }
            CloseSource closeSource = new CloseSource();
            closeSource.closeSource(connection,preparedStatement,resultSet,null);
        }catch (Exception e){
            e.printStackTrace();
        }

        return ID;
    }
}
